package com.ProtocalAndUdp;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 读取Excel 97~2003 xls格式 /2007~ xlsx格式
 * @author		ZhangLiKun
 * @mail		likun_zhang@yeah.net
 * @date		2013-5-11
 */
public class ExcelReader {

	public static final List<List<String>> readExcel(String path)throws IOException{
		 Workbook wb =createWb(path);
		 Sheet sheet = getSheet(wb, 0) ; 
		 return listFromSheet(sheet) ;
	}
	/**
	 * 创建工作簿对象
	 * @param filePath
	 * @return
	 * @throws IOException
	 * @date	2013-5-11
	 */
	private static final Workbook createWb(String filePath) throws IOException {
		if(filePath.trim().toLowerCase().endsWith("xls")) {
			return new HSSFWorkbook(new FileInputStream(filePath)) ;
		} else if(filePath.trim().toLowerCase().endsWith("xlsx")) {
			return new XSSFWorkbook(new FileInputStream(filePath)) ;
		} else {
			throw new IllegalArgumentException("不支持除：xls/xlsx以外的文件格式!!!") ;
		}
	}
	
	private static final Sheet getSheet(Workbook wb ,String sheetName) {
		return wb.getSheet(sheetName) ;
	}
	
	private static final Sheet getSheet(Workbook wb ,int index) {
		return wb.getSheetAt(index) ;
	}
	
	private static final List<List<String>> listFromSheet(Sheet sheet) {
		List<List<String>> list = new ArrayList<List<String>>() ;
		for(int r = sheet.getFirstRowNum() ; r <= sheet.getLastRowNum() ; r ++) {
			Row row = sheet.getRow(r) ;
			if(row == null)continue ;
			List<String> cells = new ArrayList<String>();
			for(int c = row.getFirstCellNum() ; c <= row.getLastCellNum() ; c++) {
				Cell cell = row.getCell(c) ;
				if(cell == null)continue ;
				cells.add(getValueFromCell(cell)) ;
			}
			list.add(cells) ;
		}
		
		return list ;
	}
	
	
	/**
	 * 获取单元格内文本信息
	 * @param cell
	 * @return
	 * @date	2013-5-8
	 */
	private static final String getValueFromCell(Cell cell) {
		if(cell == null) {
			return null ;
		}
		String value = null ;
		switch(cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC :	// 数字
				 	value = String.valueOf(cell.getNumericCellValue()) ;
				break ;
			case Cell.CELL_TYPE_STRING:		// 字符串
				value = cell.getStringCellValue() ;
				break ;
			
			case Cell.CELL_TYPE_BLANK:				// 空白
				value = "" ;
				break ;
			case Cell.CELL_TYPE_BOOLEAN:			// Boolean
				value = String.valueOf(cell.getBooleanCellValue()) ;
				break ;
			case Cell.CELL_TYPE_ERROR:				// Error，返回错误码
				value = String.valueOf(cell.getErrorCellValue()) ;
				break ;
			default:value ="" ;break ;
		}
		return value ;
	}	
	
}

